﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace bankCheckAudio
{
    public delegate void SetProBar(int cur, int all, string title, string des);
    public delegate void ChangeText(string des);

    public partial class Form1 : Form
    {

        private string pathBank;
        private string pathEvent;
        private string pathAudio;
        
        private SetProBar setProBar;
        private ChangeText changeDes;

        // 每个资源引用pathAudio  使用的 event 列表 (遍历event 路径下文件得到)
        private Dictionary<string, List<string>> dicAudioFile = new Dictionary<string, List<string>>();

        // pathAudio 实际对应到的资源路径
        private Dictionary<string, string> audioFileName = new Dictionary<string, string>();

        // bank 指向的实际名称
        private Dictionary<string, string> dicBankName = new Dictionary<string, string>();

        // event 对应实际名称 （走 guid）
        private Dictionary<string, string> dicEvenetName = new Dictionary<string, string>();

        // event 指向的 bank文件
        private Dictionary<string, string> dicEventToBank = new Dictionary<string, string>();

        // event 指向的 audioFile 文件(一个event 内会有多个 audioFile)
        private Dictionary<string, List<string>> dicEventToAuidoFile = new Dictionary<string, List<string>>();

        // 没有音效的 event 事件
        List<string> noAudioFileEvevtList = new List<string>();

        // guid 中 evnet 对应的 具体 eventName  (完整长度)
        private Dictionary<string, string> dicGUIDToEvenName = new Dictionary<string, string>();
        // (反过来 eventName - > guid)
        private Dictionary<string, string> dicGUIDToEvenName2 = new Dictionary<string, string>();

        // 导出的 eventName 列表 (audio 对应的 eventName)
        private List<List<string>> listCurOutEvent = new List<List<string>>();


        public Form1()
        {
            InitializeComponent();
            this.setProBar = this.SetCurPro;
            this.changeDes = (str) => {
                this.lab_des.Text = str;
            };
        }


        /// <summary>
        /// 点击检测
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Check_Click(object sender, EventArgs e)
        {


            this.GetAudioFileName();

            this.GetBankName();

            this.GetEventName();

            this.GetGuidToEventName();

            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "audio(*.xlsx)|*.xlsx";
            saveFileDialog.ShowDialog();
            string path = saveFileDialog.FileName;

            this.CreatExcel(path);

        }

        private void SetCurPro(int cur,int all,string title,string des)
        {
            this.lab_tip.Text = title;
            this.changeDes(des);
            //this.lab_des.Text = des;
            this.lab_pro.Text = string.Format("当前进度{0}/{1}",cur,all);
            if (cur == 0 || all == 0)
            {
                this.prog.Value = 0;
            }
            else
            {
                this.prog.Value = (int)((cur * 1.0 / all) * 100);
            }
            
        }

        /// <summary>
        /// 获取目录下所有文件
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private FileInfo[] GetFileList(string path)
        {
            DirectoryInfo TheFolder = new DirectoryInfo(path);
            FileInfo[] fileInfos = TheFolder.GetFiles();
            return fileInfos;
        }

        /// <summary>
        /// 获取 audio 资源引用最后的名称
        /// </summary>
        private void GetAudioFileName()
        {
            this.pathAudio = this.text_pathAudio.Text;

            if (Directory.Exists(this.pathAudio))
            {
                FileInfo[] fileInfos = this.GetFileList(this.pathAudio);
                int allCount = fileInfos.Length;
                int cur = 0;
                foreach (var file in fileInfos)
                {
                    this.SetAudioFileName(file.FullName);
                    cur++;
                    this.setProBar(cur, allCount,"遍历AudioFile目录", file.FullName);
                }

                foreach (var item in this.audioFileName)
                {
                    Console.Write(item);
                }
            }
        }

        /// <summary>
        /// 获取guid 中 event 标识 对应的eventName(长路径)
        /// </summary>
        private void GetGuidToEventName()
        {
            string path = this.text_pathGuid.Text;

            if (!File.Exists(path))
            {
                return;
            }
            try
            {
                using (var fs = new FileStream(path, FileMode.Open))
                {
                    int fsLen = (int)fs.Length;
                    byte[] txtByte = new byte[fsLen];
                    int r = fs.Read(txtByte, 0, txtByte.Length);
                    string myStr = System.Text.Encoding.UTF8.GetString(txtByte);
                    string[] curRowStr = myStr.Split('\r', '\n');
                    int allCount = curRowStr.Length;
                    int cur = 0;
                    for (int i = 0; i < curRowStr.Length; i++)
                    {
                        if (string.IsNullOrEmpty(curRowStr[i]) == false)
                        {
                            string[] curList = Regex.Split(curRowStr[i], "\\s+", RegexOptions.IgnoreCase);//  curRowStr[i].Split("\\s+", RegexOptions.IgnoreCase);
                            string eventkey = curList[0].Trim();
                            string eventName = curList[1].Trim();
                            //string[] temp = eventName.Split('/');
                            // string key = temp[temp.Length - 1];
                            //audioGuidKey[key] = eventName;
                            this.dicGUIDToEvenName[eventkey] = eventName;
                            this.dicGUIDToEvenName2[eventName] = eventkey;
                            cur = cur +2;
                            this.setProBar(cur, allCount, "遍历GUID", eventName);
                        }

                    }
                }
            }
            catch (Exception)
            {

                throw;
            }

        }

        /// <summary>
        /// 音效路径
        /// </summary>
        /// <param name="path"></param>
        private void SetAudioFileName(string path)
        {
            if (File.Exists(path))
            {

                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(path);

                XmlNodeList topM = xmldoc.DocumentElement.ChildNodes;
                string[] temp = path.Split('\\');
                string[] files = temp[temp.Length - 1].Split('.');
                string keyname = files[0];  // 文件名 

                foreach (XmlElement element in topM)
                {
                    XmlNodeList xmlNodeList = element.ChildNodes;
                    
                    
                    if (xmlNodeList.Count > 0)
                    {
                        foreach (XmlElement el in xmlNodeList)
                        {
                            string nodeName = this.GetNodeName(el.Attributes);
                            if (nodeName == "assetPath")
                            {
                                string assetPath = el.InnerText;
                                this.audioFileName[keyname] = assetPath;
                                break;
                            }
                        }
                    }

                }

            }
        }


        /// <summary>
        /// 获取 bank 对应名称
        /// </summary>
        private void GetBankName()
        {
            this.pathBank = this.text_pathBank.Text;

            if (Directory.Exists(this.pathBank))
            {
                FileInfo[] fileInfos = this.GetFileList(this.pathBank);
                int allCount = fileInfos.Length;
                int cur = 0;
                foreach (var file in fileInfos)
                {
                    this.SetBankName(file.FullName);
                    cur++;
                    this.setProBar(cur, allCount, "遍历Bank目录", file.FullName);
                }

                foreach (var item in this.dicBankName)
                {
                    Console.Write(item);
                }
            }
        }


        /// <summary>
        /// bank 名称
        /// </summary>
        /// <param name="path"></param>
        private void SetBankName(string path)
        {
            if (File.Exists(path))
            {

                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(path);

                XmlNodeList topM = xmldoc.DocumentElement.ChildNodes;
                string[] temp = path.Split('\\');
                string[] files = temp[temp.Length - 1].Split('.');
                string keyname = files[0];  // 文件名 

                foreach (XmlElement element in topM)
                {
                    XmlNodeList xmlNodeList = element.ChildNodes;
                   
                    if (xmlNodeList.Count > 0)
                    {
                        string nodeName = this.GetNodeName(element.Attributes);
                        if (nodeName == "Bank")
                        {
                            foreach (XmlElement el in xmlNodeList)
                            {
                                // 获取节点名称
                                nodeName = this.GetNodeName(el.Attributes);
                                if (nodeName == "name")
                                {
                                    string bankname = el.InnerText;
                                    this.dicBankName[keyname] = bankname;
                                }
                            }
                        }
                      
                    }

                }

            }
        }


        /// <summary>
        /// 获取 event 对应 bank 对应 audiofile
        /// </summary>
        private void GetEventName()
        {
            this.pathEvent = this.text_pathEvent.Text;

            this.dicEvenetName.Clear();
            this.dicEventToAuidoFile.Clear();
            this.dicEventToBank.Clear();
            this.noAudioFileEvevtList.Clear();

            if (Directory.Exists(this.pathEvent))
            {
                FileInfo[] fileInfos = this.GetFileList(this.pathEvent);
                int allCount = fileInfos.Length;
                int cur = 0;
                foreach (var file in fileInfos)
                {
                    this.SetEventName(file.FullName);
                    cur++;
                    // progressForm.SetPro("", file.FullName, cur, allCount);
                    this.setProBar(cur, allCount, "遍历Event目录", file.FullName);
                }

                //foreach (var item in this.dicEvenetName)
                //{
                //    Console.Write(item);
                //}

                //foreach (var item in this.dicEventToAuidoFile)
                //{
                //    Console.Write(item);
                //}

                //foreach (var item in this.dicEventToBank)
                //{
                //    Console.Write(item);
                //}

                //foreach (var item in this.noAudioFileEvevtList)
                //{
                //    Console.Write(item);
                //}

                //Console.Write(curfileCount);
            }
        }

        
        private int curfileCount = 0;
        /// <summary>
        /// event 数据
        /// </summary>
        /// <param name="path"></param>
        private void SetEventName(string path)
        {
            if (File.Exists(path))
            {

                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(path);

                XmlNodeList topM = xmldoc.DocumentElement.ChildNodes;
                string[] temp = path.Split('\\');
                string[] files = temp[temp.Length - 1].Split('.');
                string keyname = files[0];  // 文件名 

                int curIdx = 0;
                bool isHasSingleSong = false;
                foreach (XmlElement element in topM)
                {
                    XmlNodeList xmlNodeList = element.ChildNodes;
                 
                    // 获取节点名称
                    string nodeName = this.GetNodeName(element.Attributes); 

                    // Event 节点取  name 和 对应的 banks
                    if (nodeName == "Event")
                    {
                        // 取 指向的 banks  event 名称
                        foreach (XmlElement el in xmlNodeList)
                        {
                            XmlAttributeCollection elattr = el.Attributes;
                            XmlNode elNode = elattr.Item(0);
                            string elName = elNode.Value;

                            if (elName == "name")
                            {
                                string eventName = el.InnerText;
                                // 存的时候就存 guid 里面的
                                this.dicEvenetName[keyname] = eventName;
                            }

                            if (elName == "banks")
                            {
                                string bankname = el.InnerText;
                                this.dicEventToBank[keyname] = bankname;
                            }
                        }
                    }

                  
                    // 取 指向的 audioFile
                    if (nodeName == "SingleSound")
                    {
                        isHasSingleSong = true;
                        foreach (XmlElement el in xmlNodeList)
                        {
                            string elName = this.GetNodeName(el.Attributes);
                            if (elName == "audioFile")
                            {
                                string audioFile = el.InnerText;
                                if (string.IsNullOrEmpty(audioFile) || audioFile == "")
                                {
                                    noAudioFileEvevtList.Add(keyname);
                                }

                                if (this.dicEventToAuidoFile.ContainsKey(keyname))
                                {
                                    this.dicEventToAuidoFile[keyname].Add(audioFile);
                                }
                                else
                                {
                                    List<string> list = new List<string>();
                                    list.Add(audioFile);
                                    this.dicEventToAuidoFile[keyname] = list;
                                }

                                // 存储 audioFile 对应的 event
                                if (this.dicAudioFile.ContainsKey(audioFile))
                                {
                                    this.dicAudioFile[audioFile].Add(keyname);
                                }
                                else
                                {
                                    List<string> list = new List<string>();
                                    list.Add(keyname);
                                    this.dicAudioFile[audioFile] = list;
                                }
                                
                            }
                        }
                    }
                }

                if (isHasSingleSong == false)
                {
                    this.noAudioFileEvevtList.Add(keyname);
                }
            }
        }

        /// <summary>
        /// 获取节点名称
        /// </summary>
        /// <param name="attributeCollection"></param>
        /// <returns></returns>
        private string GetNodeName(XmlAttributeCollection attributeCollection)
        {
            XmlNode elNode = attributeCollection.Item(0);
            string elName = elNode.Value;
            return elName;
        }



        /// <summary>
        /// 资源被多少个 bank 引用
        /// </summary>
        private List<List<string>> GetDicAudioToBank()
        {
            List<List<string>> dic = new List<List<string>>();

            //音效资源 -> 取对应的 event
            foreach (var dicValue in this.dicAudioFile)
            {
                // event列表 包含的 bank数量 大于 1 则被多次引用打包
                List<string> banks = new List<string>();
                foreach (var eventName in dicValue.Value)
                {
                    if (dicEventToBank.ContainsKey(eventName))
                    {
                        string bankName = dicEventToBank[eventName];
                        string name = dicBankName[bankName];
                        if (banks.Contains(name) == false)
                        {
                            banks.Add(name);
                        }
                    }
                   
                }
                if (banks.Count > 1)
                {
                    string audioFile = audioFileName[dicValue.Key];
                    banks.Insert(0, banks.Count.ToString());
                    // 取当前音效大小
                    string assetPath = this.text_pathAsset.Text;
                    if (string.IsNullOrEmpty(assetPath) == false)
                    {
                        string curPath = string.Format(assetPath+"/{0}", audioFile);
                        //System.Diagnostics.FileVersionInfo info = System.Diagnostics.FileVersionInfo.GetVersionInfo(curPath);

                        System.IO.FileInfo fileInfo = null;
                        try
                        {
                            fileInfo = new System.IO.FileInfo(curPath);
                        }
                        catch (Exception)
                        {

                            throw;
                        }
                        if (fileInfo != null && fileInfo.Exists)
                        {
                            string size = System.Math.Ceiling(fileInfo.Length / 1024.0) +  "KB";
                            banks.Insert(0, size);
                        }
                    }

                    banks.Insert(0, audioFile);
                    dic.Add(banks);
                }
            }
            return dic;
        }

        /// <summary>
        /// 资源被多少个 event 引用
        /// </summary>
        private List<List<string>> GetDicAudioToEvent()
        {
            List<List<string>> dic = new List<List<string>>();

            //音效资源 -> 取对应的 event
            foreach (var dicValue in this.dicAudioFile)
            {
                // 
                List<string> eventNames = new List<string>();
                if (dicValue.Value.Count > 1)
                {
                    // 遍历第一次  音效是否满足被多 bank 导出
                    List<string> banks = new List<string>();
                    foreach (var eventName in dicValue.Value)
                    {
                        string bankName = dicEventToBank[eventName];
                        string name = dicBankName[bankName];
                        if (banks.Contains(name) == false)
                        {
                            banks.Add(name);
                        }
                    }
                    if (banks.Count > 1)
                    {
                        // 满足多bank 导出 再遍历 导出引用了资源的 eventname
                        foreach (var evName in dicValue.Value)
                        {
                            if (this.dicEvenetName.ContainsKey(evName))
                            {
                                if (this.dicGUIDToEvenName.ContainsKey(evName))
                                {
                                    string eventName = this.dicGUIDToEvenName[evName];  //this.dicEvenetName[evName];                                         
                                    eventNames.Add(eventName);   // eventName 要取到 guid 中的才为准
                                }
                              
                            }
                        }
                        if (eventNames.Count > 1)
                        {
                            string audioFile = audioFileName[dicValue.Key];
                            eventNames.Insert(0, eventNames.Count.ToString());
                            eventNames.Insert(0, audioFile);
                            dic.Add(eventNames);
                        }
                    }  
                }
              
            }
            return dic;
        }

        /// <summary>
        /// 创建配置表
        /// </summary>
        /// <param name="savePath"></param>
        private void CreatExcel(string savePath)
        {
            XSSFWorkbook workbook = null;
            if (savePath.LastIndexOf(".xlsx") > 0)  // 2007
            {
                workbook = new XSSFWorkbook();   // 2003
            }
            //if (savePath.LastIndexOf(".xls") > 0)
            //{
            //    workbook = new HSSFWorkbook();
            //}
            //else
            //{
            //    workbook = new HSSFWorkbook();
            //}


            // 音效被 多 bank 引用数据
            this.SaveSheetAudioToBank(ref workbook);

            // 音效被 多 Event 引用数据
            this.SaveSheetAudioToEven(ref workbook);

            // event 对应 bank 列表  (替换时  增加一列 new 用来替换 audio 配置)
            this.SaveSheetBankToEventName(ref workbook);


            this.SaveAllExcel(workbook,savePath);
        }

        /// <summary>
        /// 音效被 多 bank 引用数据
        /// </summary>
        private void SaveSheetAudioToBank(ref XSSFWorkbook workbook)
        {

            List<List<string>> header = new List<List<string>>();
            List<string> list1 = new List<string>();
            list1.Add("当前使用资源");
            list1.Add("资源大小");
            list1.Add("重复数量");
            list1.Add("使用当前资源的bank文件");
            header.Add(list1);

            var data = this.GetDicAudioToBank();

            this.CreatSheetData(header,data, "多次导出的音效资源",ref workbook);
        }

        /// <summary>
        /// 音效被 多 event 引用数据
        /// </summary>
        private void SaveSheetAudioToEven(ref XSSFWorkbook workbook)
        {

            List<List<string>> header = new List<List<string>>();
            List<string> list1 = new List<string>();
            list1.Add("当前使用资源");
            list1.Add("引用数量");
            list1.Add("引用当前资源的Event");
            header.Add(list1);

            this.listCurOutEvent = this.GetDicAudioToEvent();

            this.CreatSheetData(header, this.listCurOutEvent, "多次引用音效资源的EventName", ref workbook);
        }


        /// <summary>
        /// evenName 对应的 旧 bank
        /// </summary>
        private void SaveSheetBankToEventName(ref XSSFWorkbook workbook)
        {

            List<List<string>> header = new List<List<string>>();
            List<string> list1 = new List<string>();
            list1.Add("event名称");
            list1.Add("所在的旧Bnak");
            header.Add(list1);

            var data = this.GetDicBankToEvent();

            this.CreatSheetData(header, data, "event所在旧bank", ref workbook);
        }

        /// <summary>
        ///  evenName 对应的 旧 bank
        /// </summary>
        /// <returns></returns>
        private List<List<string>> GetDicBankToEvent()
        {
            List<List<string>> dic = new List<List<string>>();

            for (int i = 1; i < this.listCurOutEvent.Count; i++)
            {
                var eventList = this.listCurOutEvent[i];
                for (int j = 2; j < eventList.Count; j++)
                {
                    List<string> evToBank = new List<string>(eventList.Count-2);
                    string key = eventList[j];
                    evToBank.Add(key);
                   
                    if (this.dicGUIDToEvenName2.ContainsKey(key))
                    {
                        string evKey = this.dicGUIDToEvenName2[key];
                        string bankKey = this.dicEventToBank[evKey];
                        string bankName = this.dicBankName[bankKey];
                        evToBank.Add(bankName);
                    }
                    else
                    {
                        Console.Write(key);
                    }
                 
                    dic.Add(evToBank);
                }
            }

            return dic;
        }


        /// <summary>
        /// 创建excel 页数据
        /// </summary>
        private void CreatSheetData(List<List<string>> headers, List<List<string>> datas, string sheetName, ref XSSFWorkbook workbook)
        {

            ISheet sheet;   // 配置表 sheet
            var appendRow = 0;   // 列

            sheet = workbook.CreateSheet(sheetName);  // 
            sheet.DefaultColumnWidth = 40;

            // 数据头插到数据前面
            datas.InsertRange(0, headers);

            foreach (List<string> values in datas)
            {
                var row = sheet.CreateRow(appendRow);
                var cellnum = 0;  // 行
                foreach (string value in values)
                {
                    var cell = row.CreateCell(cellnum);
                    cell.SetCellValue(value);
                    cellnum++;
                }
                appendRow++;
            }
        }

        private void SaveAllExcel(IWorkbook workbook,string savePath)
        {
            FileStream fs = null;
            try
            {
                fs = new FileStream(savePath, FileMode.OpenOrCreate);
                if (fs.CanWrite)
                {
                    workbook.Write(fs);
                    workbook.Close();
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                }

            }
            MessageBox.Show("导出完成");
        }

        //===================================  加载替换  ===========================================


        private List<List<string>> list_audioConfig;
        private List<List<string>> list_newBankConfig;
        private List<List<string>> listChangeName = new List<List<string>>();


        private void btn_changeNewBank_Click(object sender, EventArgs e)
        {
            this.GetAudioConfig();
            this.GetNewBankConfig();

            this.SaveNewAudioConfig();
            this.SaveAudioConfig();

        }

        /// <summary>
        /// 保存文件
        /// </summary>
        private void SaveAudioConfig()
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "audio(*.xlsx)|*.xlsx";
            saveFileDialog.ShowDialog();
            string savePath = saveFileDialog.FileName;

            XSSFWorkbook workbook = null;
            if (savePath.LastIndexOf(".xlsx") > 0)  // 2007
            {
                workbook = new XSSFWorkbook();   // 2003
            }

            this.SaveSheetAudioNewConfig(ref workbook);
            this.SaveSheetChangeName(ref workbook);

            this.SaveAllExcel(workbook, savePath);

        }

        private void SaveSheetAudioNewConfig(ref XSSFWorkbook workbook)
        {

            List<List<string>> header = new List<List<string>>();


            var data = this.list_audioConfig;

            this.CreatSheetData(header, data, "export_Audio", ref workbook);
        }

        /// <summary>
        /// 变化的列表
        /// </summary>
        /// <param name="workbook"></param>
        private void SaveSheetChangeName(ref XSSFWorkbook workbook)
        {

            List<List<string>> header = new List<List<string>>();

            var data = this.listChangeName;

            this.CreatSheetData(header, data, "export_name", ref workbook);
        }

        /// <summary>
        ///  加载audio 配置
        /// </summary>
        private void GetAudioConfig()
        {
            string path = this.text_pathAudioConfig.Text;
            this.list_audioConfig = this.GetExcelConfingData(path,0);

        }

        /// <summary>
        /// 加载新的bank配置
        /// </summary>
        private void GetNewBankConfig()
        {
            string path = this.text_pathNewBank.Text;
            this.list_newBankConfig = this.GetExcelConfingData(path, 2);
        }

        /// <summary>
        /// 替换并保存新的 audio 配置
        /// </summary>
        private void SaveNewAudioConfig()
        {
            int cur = 0;
            int all = this.list_audioConfig.Count;
            foreach (var audioCell in this.list_audioConfig)
            {
                // 取第三列 eventname 来判断
                string eventName = audioCell[2];   
                if (string.IsNullOrEmpty(eventName) == false)
                {
                    foreach (var newBank in list_newBankConfig)
                    {
                        string curEvName = newBank[0];
                        if (eventName == curEvName)
                        {
                            string newB = newBank[2];
                            if (audioCell[1] != newB)
                            {
                                audioCell[1] = newB;

                                List<string> changName = new List<string>();
                                changName.Add(audioCell[0]);
                                this.listChangeName.Add(changName);
                            }
                            break;
                        }
                    }
                }
                cur++;
                this.setProBar(cur,all,"导出新audio配置","");
            }

        }

        /// <summary>
        /// 读取excel 配置数据
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private List<List<string>> GetExcelConfingData(string openPath,int sheetIdx)
        {
            List<List<string>> datas = new List<List<string>>();

            if (!File.Exists(openPath))
            {
                return datas;
            }
            try
            {
                using (var fs = new FileStream(openPath, FileMode.Open))
                {
                    IWorkbook workbook = null;
                    // 2007版本  
                    if (openPath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (openPath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    else
                        workbook = new HSSFWorkbook(fs);

                    if (workbook.NumberOfSheets > 0)
                    {
                        var sheet = workbook.GetSheetAt(sheetIdx);
                        if (sheet != null && sheet.LastRowNum > 0)
                        {
                            ICell cell = null;
                            IRow firstRow = sheet.GetRow(5);//第一行  
                            int cellCount = firstRow.LastCellNum;//列数
                            for (var rownum = 0; rownum <= sheet.LastRowNum; rownum++)
                            {
                                try
                                {
                                    string _val_ = "";
                                    var row = sheet.GetRow(rownum);
                                    if (row == null)
                                    {
                                        continue;
                                    }
                                    cell = row.GetCell(0);
                                    if (cell == null)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        _val_ = cell.StringCellValue;
                                        if (string.IsNullOrEmpty(_val_))
                                        {
                                            continue;
                                        }
                                    }
                                    var data = new List<string>();
                                    for (int j = 0; j < cellCount; ++j)
                                    {
                                        try
                                        {
                                            cell = row.GetCell(j);
                                            if (cell == null)
                                            {
                                                _val_ = "";
                                            }
                                            else
                                            {
                                                _val_ = "";
                                                try
                                                {
                                                    switch (cell.CellType)
                                                    {
                                                        case CellType.Numeric:
                                                            _val_ = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                                                            break;

                                                        case CellType.Boolean:
                                                            _val_ = cell.BooleanCellValue.ToString(CultureInfo.InvariantCulture);
                                                            break;

                                                        case CellType.String:
                                                            _val_ = cell.StringCellValue;
                                                            break;

                                                        default:
                                                            _val_ = cell.StringCellValue;
                                                            break;
                                                    }
                                                }
                                                catch (Exception e)
                                                {
                                                    _val_ = "";
                                                }
                                            }
                                            data.Add(_val_);
                                      
                                        }
                                        catch (Exception e)
                                        {
                                          
                                            return null;
                                        }
                                    }
                                    datas.Add(data);
                                }
                                catch (Exception e)
                                {
                                 
                                    return null;
                                }
                            }
                        }
                    }
                    fs.Close();
                    fs.Dispose();
                }
            }
            catch (Exception)
            {

                throw;
            }


            return datas;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}
